-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Tidus
-- Create date: 20/8/2011
-- Description:	Proc TimeSheet
-- =============================================

USE QLNS
GO
IF OBJECT_ID ('sp_InsertTs', 'p') IS NOT NULL
	DROP PROC sp_InsertTs
Go
CREATE PROCEDURE sp_InsertTs
	@Em_Id varchar(7),
	@strDate varchar(50),
	@Mor bit,
	@Aft bit,
	@Eve bit,
	@UserId int
AS
BEGIN
	SET NOCOUNT ON;
	SET XACT_ABORT ON;
	BEGIN TRANSACTION;
	BEGIN TRY
		--[0]--
		DECLARE @Now datetime;
		EXEC sp_Convert_Varchar_Datetime @strDate, @Now OUTPUT;
		--[1]--
		INSERT INTO TimeSheet(TS_Date, TS_Morning, TS_Afternoon, TS_Evening, Em_Id, UserInsert, DateInsert)
			VALUES (@Now, @Mor, @Aft, @Eve, @Em_Id, @UserId, GETDATE())
		COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		ROLLBACK TRANSACTION
	END CATCH
END
GO

IF OBJECT_ID ('sp_UpdateTs', 'p') IS NOT NULL
	DROP PROC sp_UpdateTs
Go
CREATE PROCEDURE sp_UpdateTs
	@Em_Id varchar(7),
	@strDate varchar(50),
	@Mor bit,
	@Aft bit,
	@Eve bit,
	@UserId int
AS
BEGIN
	SET NOCOUNT ON;
	SET XACT_ABORT ON;
	BEGIN TRANSACTION;
	BEGIN TRY
		--[0]--
		DECLARE @Date datetime;
		EXEC sp_Convert_Varchar_Datetime @strDate, @Date OUTPUT;
		--[1]--
		DECLARE @count int;
		SELECT @count = COUNT(*) FROM TimeSheet WHERE Em_Id = @Em_Id AND TS_Date = @Date;
		IF(@count = 0)
			INSERT INTO TimeSheet(TS_Date, TS_Morning, TS_Afternoon, TS_Evening, Em_Id, UserUpdate, DateUpdate)
				VALUES (@Date, @Mor, @Aft, @Eve, @Em_Id, @UserId, GETDATE())
		ELSE
			UPDATE TimeSheet
				SET	TS_Morning = @Mor, TS_Afternoon = @Aft, TS_Evening = @Eve, 
					UserUpdate = @UserId, DateUpdate = GETDATE()
				WHERE TS_Date = @Date AND Em_Id = @Em_Id
		COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		ROLLBACK TRANSACTION
	END CATCH
END
GO
/*
EXEC sp_UpdateTs 'NV-0002', '31/7/2011', true, false, true, 1
DECLARE @count int;
SELECT @count = COUNT(*) FROM TimeSheet WHERE Em_Id = 'NV-0002' AND TS_Date = '2011-7-27';
SELECT * FROM TimeSheet WHERE Em_Id = 'NV-0002'
*/